package com.bothin.experiment4.dao;

import com.bothin.experiment4.dto.StatisticDTO;
import com.bothin.experiment4.model.Customer;
import com.bothin.experiment4.model.Product;
import org.apache.ibatis.annotations.Select;
import tk.mybatis.mapper.common.Mapper;

import java.util.List;

public interface ProductMapper extends Mapper<Product> {
    @Select("SELECT * FROM `product`\n" +
            "WHERE product_name LIKE #{key}")
    public List<Product> findByKeyword(String key);

    @Select("SELECT product_price productPrice FROM `product` WHERE product_id = #{productId}")
    public Integer selectPriceByProductId(String productId);

    @Select("SELECT product_name name, SUM(contact_detail.product_quantity)*product_price value \n" +
            "FROM `product` LEFT JOIN contact_detail on product.product_id = contact_detail.product_id\n" +
            "WHERE contact_detail.contact_id in \n" +
            "(SELECT contact_id FROM contact WHERE create_time BETWEEN #{start} AND #{end} )\n" +
            "GROUP BY product.product_id\n" +
            "ORDER BY value DESC\n" +
            "LIMIT 0,#{limit}")
    public List<StatisticDTO> getAllProductSStatistic1(String start, String end, int limit);

    @Select("SELECT product_name name, SUM(contact_detail.product_quantity)*product_price value \n" +
            "FROM `product` LEFT JOIN contact_detail on product.product_id = contact_detail.product_id\n" +
            "WHERE contact_detail.contact_id in \n" +
            "(SELECT contact_id FROM contact)\n" +
            "GROUP BY product.product_id\n" +
            "ORDER BY value DESC\n" +
            "LIMIT 0,#{limit}")
    public List<StatisticDTO> getAllProductSStatistic2(int limit);

    @Select("SELECT product_name name, SUM(contact_detail.product_quantity)*product_price value \n" +
            "FROM `product` LEFT JOIN contact_detail on product.product_id = contact_detail.product_id\n" +
            "WHERE contact_detail.contact_id in \n" +
            "(SELECT contact_id FROM contact)\n" +
            "GROUP BY product.product_id\n" +
            "ORDER BY value DESC\n" )
    public List<StatisticDTO> getAllProductSStatistic3();
}